﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Windows.Forms;
using System.Threading;
using Oracle.ManagedDataAccess.Client;
using System.Data.OleDb;
using System.Net.NetworkInformation;
using System.Text.RegularExpressions;
using Titan.FIS.Enum;
using static System.Windows.Forms.VisualStyles.VisualStyleElement.TaskbarClock;
using Microsoft.Office.Interop.Word;
using System.Runtime.CompilerServices;
using Wantn.FIS.LmsDataSync;
using System.IO;
using Titan.FIS.UI;

namespace Titan.FIS.LmsDataSync
{
    public partial class frmDataSyncMain : Form
    {
        /// <summary>
        /// 自动同步线程取消控制实例
        /// </summary>
        private CancellationTokenSource _ctsSyncThreadAuto;
        /// <summary>
        /// 自动同步线程是否正在运行
        /// </summary>
        private bool _bSyncThreadRunning;
        /// <summary>
        /// 自动提同步线程
        /// </summary>
        private Thread _thSyncThreadAuto;


        /// <summary>
        /// 所有需要采集的化验指标和对应的化验仪器
        /// </summary>
        private Dictionary<LaboratoryIndex, List<LaboratoryInstrument>> _dtLaboratoryInfo;
        /// <summary>
        /// 每台化验仪器的数据库相关信息
        /// </summary>
        private Dictionary<LaboratoryInstrument, ListItem> _dtLaboratoryDataBaseInfo;
        /// <summary>
        /// 化验指标和对应的采集数据库中的列
        /// </summary>
        private Dictionary<LaboratoryIndex, ListItem> _dtLaboratoryDataBaseIndex;

        /// <summary>
        /// 自动同步时间间隔
        /// </summary>
        private int _iAutoSyncTimeGap;

        /// <summary>
        /// 自动同步几天前的数据
        /// </summary>
        private int _iAutoSyncDay;

        /// <summary>
        /// 构造函数
        /// </summary>
        public frmDataSyncMain()
        {
            InitializeComponent();
        }

        /// <summary>
        /// 窗口加载事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void frmLMSNotify_Load(object sender, EventArgs e)
        {

            Visible = false;//启动任务区不可见
            notifyIconLMS.Visible = true; //托盘区可见

            Common.UserName = System.Configuration.ConfigurationManager.AppSettings["Oracle_UserID"];//oracle数据库用户名
            Common.UserPwd = System.Configuration.ConfigurationManager.AppSettings["Oracle_PassWord"];//oracle数据库密码

            try
            {
                _iAutoSyncTimeGap = int.Parse(System.Configuration.ConfigurationManager.AppSettings["AutoSyncTimeGap"]);
                _iAutoSyncDay = -1 * int.Parse(System.Configuration.ConfigurationManager.AppSettings["AutoSyncDay"]);
            }
            catch (Exception ex)
            {
                Titan.FIS.Log.LogHelper.DoWrite(Titan.FIS.Log.LogType.Error, "自动同步间隔时间配置有误，请输入有效整数" + ex.Message);
            }

            //量热仪数据库1的网络路径，以下类推
            var strSourcePath1LrSdc608 = System.Configuration.ConfigurationManager.AppSettings["LR_SDC608SourcePath1"];

            //定硫仪
            var strSourcePath1DlSds616 = System.Configuration.ConfigurationManager.AppSettings["DL_SDS616SourcePath1"];
            var strSourcePath2DlSds616 = System.Configuration.ConfigurationManager.AppSettings["DL_SDS616SourcePath2"];
            var strSourcePath3DlSds616 = System.Configuration.ConfigurationManager.AppSettings["DL_SDS616SourcePath3"];

            var strSourcePath1SfSdtga408 = System.Configuration.ConfigurationManager.AppSettings["SF_SDTGA408SourcePath1"];
            var strSourcePath1GfSdtga8000 = System.Configuration.ConfigurationManager.AppSettings["GF_SDTGA8000SourcePath1"];
            var strSourcePath2GfSdtga8000 = System.Configuration.ConfigurationManager.AppSettings["GF_SDTGA8000SourcePath2"];
            var strG151SourcePath = System.Configuration.ConfigurationManager.AppSettings["G151SourcePath"];
            var strG152SourcePath = System.Configuration.ConfigurationManager.AppSettings["G152SourcePath"];
            var strY121SourcePath = System.Configuration.ConfigurationManager.AppSettings["Y121SourcePath"];
            var strY122SourcePath = System.Configuration.ConfigurationManager.AppSettings["Y122SourcePath"];

            var strY124SourcePath = System.Configuration.ConfigurationManager.AppSettings["Y124SourcePath"];
            var strY125SourcePath = System.Configuration.ConfigurationManager.AppSettings["Y125SourcePath"];
            var strY126SourcePath = System.Configuration.ConfigurationManager.AppSettings["Y126SourcePath"];

            //量热仪数据库1的密码，以下类推
            const string strLrPwd1 = "199302";
            const string strDlPwd1 = "199302";
            const string strDlPwd2 = "199302";
            const string strDlPwd3 = "gkf";
            const string strSfPwd1 = "";
            const string strGfPwd1 = "";
            const string strGfPwd2 = "";
            const string strGPwd1 = "";
            const string strGPwd2 = "";
            const string strYPwd = "";

            //量热仪数据库的查询列，以下类推
            const string strSelectQbadColumn = @"AutoNo as AutoNo,
                                    ManualNumber as TestCode,
                                    SampleWeight as SampleWeight,
                                    int((Qbad+0.005)*100)/100 as TestValue,
                                    TestDate as TestDate
                                   ";
            const string strSelectStadColumn = @"AutoNo as AutoNo,
                                    SampleNo as TestCode,
                                    Weight as SampleWeight,
                                    Stad as TestValue, 
                                    CDATE(FORMAT(TestDate,'YYYY-MM-DD') + ' ' + FORMAT(EndTime,'HH:mm:ss')) as TestDate 
                                   ";
            const string strSelectMtColumn = @"序号 as AutoNo,
                                    样品编号 as TestCode,
                                    起始重量 as SampleWeight,
                                    含水百分比 as TestValue,                                    
                                    CDATE(试验开始时间) AS TestDate
                                   ";
            const string strSelectMadColumn = @"AutoNo as AutoNo,
                                    SampleNo as TestCode,
                                    MMass as SampleWeight,
                                    Mad as TestValue,                                    
                                    CDATE(FORMAT(TestDate,'YYYY-MM-DD') + ' ' + FORMAT(TestTime,'HH:mm:ss')) AS TestDate  
                                   ";
            const string strSelectAadColumn = @"AutoNo as AutoNo,
                                    SampleNo as TestCode,
                                    AMass as SampleWeight,
                                    Aad as TestValue, 
                                    CDATE(FORMAT(TestDate,'YYYY-MM-DD') + ' ' + FORMAT(ATIME,'HH:mm:ss')) AS TestDate
                                   ";
            const string strSelectVadColumn = @"AutoNo as AutoNo,
                                    SampleNo as TestCode,
                                    VMass as SampleWeight,
                                    Vad as TestValue, 
                                    CDATE(FORMAT(TestDate,'YYYY-MM-DD') + ' ' + FORMAT(VTIME,'HH:mm:ss')) AS TestDate
                                   ";

            const string strSelectGColumn = @"编号 as AutoNo,
                                    MeiName as TestCode,
                                    ZL as SampleWeight,
                                    G as TestValue, 
                                    DectedTime AS TestDate
                                   ";
            const string strSelectYColumn = @"编号 as AutoNo,
                                    YangName as TestCode,
                                    0 as SampleWeight,
                                    Val(HouMax) as TestValue, 
                                    CDATE(CeTime) AS TestDate
                                   ";
            //量热仪数据库1的表名，以下类推
            const string strLrTableName1 = "FResultdata";
            const string strDlTableName1 = "TestData";
            const string strDlTableName2 = "TestData";
            const string strDlTableName3 = "STableB";
            const string strSfTableName1 = "试验记录";
            const string strGfTableName1 = "TestData";
            const string strGfTableName2 = "TestData";
            const string strGTableName1 = "ZJ151";
            const string strGTableName2 = "ZJ151";
            const string strYQTableName = "(Select * from PrintQ where CeTime IS NOT NULL AND CeTime <> '' AND HouMax IS NOT NULL AND Val(HouMax) > 0) T";
            const string strYHTableName = "(Select * from PrintH where CeTime IS NOT NULL AND CeTime <> '' AND HouMax IS NOT NULL AND Val(HouMax) > 0) T";

            //量热仪数据库1的查询条件，以下类推
            const string strWhereQbadColumn = "Qbad IS NOT NULL AND Qbad > 0 AND TestDate >= DateAdd('d',-2,Date())";
            const string strWhereStadColumn = "Stad IS NOT NULL AND Val(Stad) > 0 AND TestDate >= DateAdd('d',-2,Date())";
            const string strWhereMtColumn = "含水百分比 IS NOT NULL AND 含水百分比 > 0 AND CDATE(试验开始时间) >= DateAdd('d',-2,Date())";
            const string strWhereMadColumn = "Mad IS NOT NULL AND Mad > 0 AND TestDate >= DateAdd('d',-2,Date())";
            const string strWhereAadColumn = "Aad IS NOT NULL AND Mad > 0 AND TestDate >= DateAdd('d',-2,Date())";
            const string strWhereVadColumn = "Vad IS NOT NULL AND Mad > 0 AND TestDate >= DateAdd('d',-2,Date())";
            const string strWhereGColumn = "G IS NOT NULL AND Val(G) > 0 AND DectedTime >= DateAdd('d',-2,Date())";
            const string strWhereYColumn = " HouMax IS NOT NULL AND Val(HouMax) > 0 AND CDATE(CeTime) >= DateAdd('d',-2,Date())  ";

            var listItemQbad = new ListItem(strSelectQbadColumn, strWhereQbadColumn, "", "");
            var listItemStad = new ListItem(strSelectStadColumn, strWhereStadColumn, "", "");
            var listItemMt = new ListItem(strSelectMtColumn, strWhereMtColumn, "", "");
            var listItemMad = new ListItem(strSelectMadColumn, strWhereMadColumn, "", "");
            var listItemAad = new ListItem(strSelectAadColumn, strWhereAadColumn, "", "");
            var listItemVad = new ListItem(strSelectVadColumn, strWhereVadColumn, "", "");
            var listItemG = new ListItem(strSelectGColumn, strWhereGColumn, "", "");
            var listItemY = new ListItem(strSelectYColumn, strWhereYColumn, "", "");

            var listItemLr1 = new ListItem(strSourcePath1LrSdc608, "admin", strLrPwd1, strLrTableName1);
            var listItemDl1 = new ListItem(strSourcePath1DlSds616, "admin", strDlPwd1, strDlTableName1);
            var listItemDl2 = new ListItem(strSourcePath2DlSds616, "admin", strDlPwd2, strDlTableName2);
            var listItemDl3 = new ListItem(strSourcePath3DlSds616, "admin", strDlPwd3, strDlTableName3);

            var listItemSf1 = new ListItem(strSourcePath1SfSdtga408, "admin", strSfPwd1, strSfTableName1);
            var listItemGf1 = new ListItem(strSourcePath1GfSdtga8000, "admin", strGfPwd1, strGfTableName1);
            var listItemGf2 = new ListItem(strSourcePath2GfSdtga8000, "admin", strGfPwd2, strGfTableName2);
            var listItemG1 = new ListItem(strG151SourcePath, "admin", strGPwd1, strGTableName1);
            var listItemG2 = new ListItem(strG152SourcePath, "admin", strGPwd2, strGTableName2);
            var listItemYQ1 = new ListItem(strY121SourcePath, "admin", strYPwd, strYQTableName);
            var listItemYH1 = new ListItem(strY121SourcePath, "admin", strYPwd, strYHTableName);
            var listItemYQ2 = new ListItem(strY122SourcePath, "admin", strYPwd, strYQTableName);
            var listItemYH2 = new ListItem(strY122SourcePath, "admin", strYPwd, strYHTableName);

            var listItemYQ3 = new ListItem(strY124SourcePath, "admin", strYPwd, strYQTableName);
            var listItemYH3 = new ListItem(strY124SourcePath, "admin", strYPwd, strYHTableName);

            var listItemYQ4 = new ListItem(strY125SourcePath, "admin", strYPwd, strYQTableName);
            var listItemYH4 = new ListItem(strY125SourcePath, "admin", strYPwd, strYHTableName);

            var listItemYQ5 = new ListItem(strY126SourcePath, "admin", strYPwd, strYQTableName);
            var listItemYH5 = new ListItem(strY126SourcePath, "admin", strYPwd, strYHTableName);

            //初始化化验仪器的数据库信息
            _dtLaboratoryDataBaseInfo = new Dictionary<LaboratoryInstrument, ListItem>
            {
                {LaboratoryInstrument.LR_A, listItemLr1},
                {LaboratoryInstrument.DL_A, listItemDl1},
                {LaboratoryInstrument.DL_B, listItemDl2},
                {LaboratoryInstrument.DL_C, listItemDl3},
                {LaboratoryInstrument.SF_A, listItemSf1},
                {LaboratoryInstrument.GF_A, listItemGf1},
                {LaboratoryInstrument.GF_B, listItemGf2},
                {LaboratoryInstrument.ZJ_A, listItemG1},
                {LaboratoryInstrument.ZJ_B, listItemG2},
                {LaboratoryInstrument.JZ_AQ, listItemYQ1},
                {LaboratoryInstrument.JZ_AH, listItemYH1},
                {LaboratoryInstrument.JZ_BQ, listItemYQ2},
                {LaboratoryInstrument.JZ_BH, listItemYH2},

                {LaboratoryInstrument.JZ_CQ, listItemYQ3},
                {LaboratoryInstrument.JZ_CH, listItemYH3},
                {LaboratoryInstrument.JZ_DQ, listItemYQ4},
                {LaboratoryInstrument.JZ_DH, listItemYH4},
                {LaboratoryInstrument.JZ_EQ, listItemYQ5},
                {LaboratoryInstrument.JZ_EH, listItemYH5}
            };
            //初始化化验指标对应采集数据库中的列
            _dtLaboratoryDataBaseIndex = new Dictionary<LaboratoryIndex, ListItem>
            {
                {LaboratoryIndex.QBAD, listItemQbad},
                {LaboratoryIndex.STAD, listItemStad},
                {LaboratoryIndex.MT, listItemMt},
                {LaboratoryIndex.MAD, listItemMad},
                {LaboratoryIndex.AAD, listItemAad},
                {LaboratoryIndex.VAD, listItemVad},
                {LaboratoryIndex.G, listItemG},
                {LaboratoryIndex.Y, listItemY}
            };

            //初始化化验指标对应的化验仪器
            _dtLaboratoryInfo = new Dictionary<LaboratoryIndex, List<LaboratoryInstrument>>
                {
                    {
                        LaboratoryIndex.QBAD, new List<LaboratoryInstrument>
                        {
                            LaboratoryInstrument.LR_A
                        }
                    },
                    {
                        LaboratoryIndex.STAD, new List<LaboratoryInstrument>
                        {
                            LaboratoryInstrument.DL_A,
                            LaboratoryInstrument.DL_B,
                            LaboratoryInstrument.DL_C
                        }
                    },
                    {
                        LaboratoryIndex.MT, new List<LaboratoryInstrument>
                        {
                            LaboratoryInstrument.SF_A
                        }
                    },
                    {
                        LaboratoryIndex.MAD, new List<LaboratoryInstrument>
                        {
                            LaboratoryInstrument.GF_A,
                            LaboratoryInstrument.GF_B
                        }
                    },
                    {
                        LaboratoryIndex.G, new List<LaboratoryInstrument>
                        {
                            LaboratoryInstrument.ZJ_A,
                            LaboratoryInstrument.ZJ_B
                        }
                    },
                    {
                        LaboratoryIndex.Y, new List<LaboratoryInstrument>
                        {
                            LaboratoryInstrument.JZ_AQ,
                            LaboratoryInstrument.JZ_AH,
                            LaboratoryInstrument.JZ_BQ,
                            LaboratoryInstrument.JZ_BH,
                            LaboratoryInstrument.JZ_CQ,
                            LaboratoryInstrument.JZ_CH,
                            LaboratoryInstrument.JZ_DQ,
                            LaboratoryInstrument.JZ_DH,
                            LaboratoryInstrument.JZ_EQ,
                            LaboratoryInstrument.JZ_EH
                        }
                    }
                };

            _ctsSyncThreadAuto = new CancellationTokenSource();//自动提交线程取消控制复位，每次用时需要重新new           
            _thSyncThreadAuto = new Thread(StartSyncThreadAuto) { IsBackground = true };
            _thSyncThreadAuto.Start();
        }

        /// <summary>
        /// 窗体退出事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void frmLMSNotify_FormClosing(object sender, FormClosingEventArgs e)
        {
            e.Cancel = true; // 取消关闭窗体 
            Hide();
            ShowInTaskbar = false;//取消窗体在任务栏的显示 
            notifyIconLMS.Visible = true;//显示托盘图标 
        }

        /// <summary>
        /// 点击显示按钮事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void Display_Click(object sender, EventArgs e)
        {
            Visible = true;
            WindowState = FormWindowState.Normal;
        }

        /// <summary>
        /// 点击退出按钮事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void Quit_Click(object sender, EventArgs e)
        {
            Quit.Enabled = false; //单击退出后 退出选项为不可用
            if (MessageBox.Show("是否确认退出化验采集后台程序?", "退出", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
            {
                //  Close(); //关闭当前窗口    
                if (_bSyncThreadRunning)
                {
                    _ctsSyncThreadAuto.Cancel();//取消线程继续运行
                }
                //while ( _bSyncThreadRunning)//等待线程退出
                //{
                //    Thread.Sleep(100);
                //}
                System.Windows.Forms.Application.ExitThread();
                System.Windows.Forms.Application.Exit();   //整个应用程序退出
            }
            else
            {
                Quit.Enabled = true;
            }
        }

        /// <summary>
        /// 窗口尺寸，位置等发生变成，即重绘事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void frmLMSNotify_Resize(object sender, EventArgs e)
        {
            if (WindowState == FormWindowState.Minimized)
            {
                Visible = false;
                notifyIconLMS.Visible = true;
            }
            else
            {
                notifyIconLMS.Visible = false;
            }
        }

        /// <summary>
        /// 双击托盘中本程序图标事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void notifyIconLMS_MouseDoubleClick(object sender, MouseEventArgs e)
        {
            Visible = true;
            WindowState = FormWindowState.Normal;
        }

        /// <summary>
        /// 立即同步按钮事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnSync_Click(object sender, EventArgs e)
        {
            var SyncThreadNow = new Thread(StartSyncThreadNow) { IsBackground = true };
            SyncThreadNow.Start();
        }

        /// <summary>
        /// 立即同步线程执行函数
        /// </summary>
        private void StartSyncThreadNow()
        {
            if (_bSyncThreadRunning)
            {
                _ctsSyncThreadAuto.Cancel();//取消线程继续运行
            }

            while (_bSyncThreadRunning)//等待线程退出
            {
                Thread.Sleep(100);
            }
            Thread.Sleep(100);

            _ctsSyncThreadAuto = new CancellationTokenSource();//自动提交线程取消控制复位，每次用时需要重新new  
            _thSyncThreadAuto = new Thread(StartSyncThreadAuto) { IsBackground = true };
            _thSyncThreadAuto.Start();
        }

        /// <summary>
        /// 自动同步线程执行函数
        /// </summary>
        private void StartSyncThreadAuto()
        {
            //线程已经开始运行    
            FormHelper.ApplicationShow(ricMsg, DisplayType.Error, "开始同步主线程");
            _bSyncThreadRunning = true;
            var iTick = 0;
            while (!_ctsSyncThreadAuto.Token.IsCancellationRequested)
            {
                var runTimeBegin = System.DateTime.Now.Ticks;

                var run = true;
                while (run)
                {
                    if (_ctsSyncThreadAuto.Token.IsCancellationRequested)
                    {/////是否取消任务，立即退出线程
                        _bSyncThreadRunning = false;
                        return;
                    }

                    NewSyncDataInfo(new CollectModel
                    {
                        AUTO_NO = "序号",
                        TEST_CODE = "前炉煤样名称",
                        TEST_SAMPLEWEIGHT = "0",
                        TEST_VALUE = "Val(前炉X值)",
                        TEST_TIME = "CDATE(创建时间)",
                        TABLE = "实验结果",
                        WHERE = $"前炉X值 IS NOT NULL AND Val(前炉X值) > 0 AND CDATE(创建时间) >= DateAdd('d',{_iAutoSyncDay},Date())",
                        ConnectionStr = $@"Provider = Microsoft.Jet.OLEDB.4.0;Data Source =\\192.168.8.126\方嘉\jz01.mdb;Mode = ReadWrite|Share Deny None;Persist Security Info = False;Jet OLEDB:DataBase Password=",
                        INSTRUMENT = (int)LaboratoryInstrument.JZ_AQ,
                        INDEX_ID = (int)LaboratoryIndex.X
                    });

                    NewSyncDataInfo(new CollectModel
                    {
                        AUTO_NO = "序号",
                        TEST_CODE = "后炉煤样名称",
                        TEST_SAMPLEWEIGHT = "0",
                        TEST_VALUE = "Val(后炉X值)",
                        TEST_TIME = "CDATE(创建时间)",
                        TABLE = "实验结果",
                        WHERE = $"后炉X值 IS NOT NULL AND Val(后炉X值) > 0 AND CDATE(创建时间) >= DateAdd('d',{_iAutoSyncDay},Date())",
                        ConnectionStr = $@"Provider = Microsoft.Jet.OLEDB.4.0;Data Source =\\192.168.8.126\方嘉\jz01.mdb;Mode = ReadWrite|Share Deny None;Persist Security Info = False;Jet OLEDB:DataBase Password=",
                        INSTRUMENT = (int)LaboratoryInstrument.JZ_AH,
                        INDEX_ID = (int)LaboratoryIndex.X
                    });

                    //NewSyncDataInfo(new CollectModel
                    //{
                    //    AUTO_NO = "序号",
                    //    TEST_CODE = "前炉煤样名称",
                    //    TEST_SAMPLEWEIGHT = "0",
                    //    TEST_VALUE = "Val(前炉X值)",
                    //    TEST_TIME = "CDATE(创建时间)",
                    //    TABLE = "实验结果",
                    //    WHERE = $"前炉X值 IS NOT NULL AND Val(前炉X值) > 0 AND CDATE(创建时间) >= DateAdd('d',{_iAutoSyncDay},Date())",
                    //    ConnectionStr = $@"Provider = Microsoft.Jet.OLEDB.4.0;Data Source =\\192.168.8.125\方嘉\jz01.mdb;Mode = ReadWrite|Share Deny None;Persist Security Info = False;Jet OLEDB:DataBase Password=",
                    //    INSTRUMENT = (int)LaboratoryInstrument.JZ_BQ,
                    //    INDEX_ID = (int)LaboratoryIndex.X
                    //});

                    //NewSyncDataInfo(new CollectModel
                    //{
                    //    AUTO_NO = "序号",
                    //    TEST_CODE = "后炉煤样名称",
                    //    TEST_SAMPLEWEIGHT = "0",
                    //    TEST_VALUE = "Val(后炉X值)",
                    //    TEST_TIME = "CDATE(创建时间)",
                    //    TABLE = "实验结果",
                    //    WHERE = $"后炉X值 IS NOT NULL AND Val(后炉X值) > 0 AND CDATE(创建时间) >= DateAdd('d',{_iAutoSyncDay},Date())",
                    //    ConnectionStr = $@"Provider = Microsoft.Jet.OLEDB.4.0;Data Source =\\192.168.8.125\方嘉\jz01.mdb;Mode = ReadWrite|Share Deny None;Persist Security Info = False;Jet OLEDB:DataBase Password=",
                    //    INSTRUMENT = (int)LaboratoryInstrument.JZ_BH,
                    //    INDEX_ID = (int)LaboratoryIndex.X
                    //});

                    #region 192.168.8.125 

                    NewSyncDataInfo(new CollectModel
                    {
                        AUTO_NO = "ID",
                        TEST_CODE = "FrontFurnaceName",
                        TEST_SAMPLEWEIGHT = "0",
                        TEST_VALUE = "Val(FrontFurnaceX)",
                        TEST_TIME = "CDATE(DateTime)",
                        TABLE = "coal",
                        WHERE = $"FrontFurnaceX IS NOT NULL AND Val(FrontFurnaceX) > 0 AND CDATE(DateTime) >= DateAdd('d',{_iAutoSyncDay},Date())",
                        ConnectionStr = $@"Provider = Microsoft.Jet.OLEDB.4.0;Data Source =\\192.168.8.125\胶质层测定\Database.mdb;Mode = ReadWrite|Share Deny None;Persist Security Info = False;Jet OLEDB:DataBase Password=",
                        INSTRUMENT = (int)LaboratoryInstrument.JZ_BQ,
                        INDEX_ID = (int)LaboratoryIndex.X
                    });

                    NewSyncDataInfo(new CollectModel
                    {
                        AUTO_NO = "ID",
                        TEST_CODE = "BackFurnaceName",
                        TEST_SAMPLEWEIGHT = "0",
                        TEST_VALUE = "Val(BackFurnaceX)", 
                        TEST_TIME = "CDATE(DateTime)",
                        TABLE = "coal",
                        WHERE = $"BackFurnaceX IS NOT NULL AND Val(BackFurnaceX) > 0 AND CDATE(DateTime) >= DateAdd('d',{_iAutoSyncDay},Date())",
                        ConnectionStr = $@"Provider = Microsoft.Jet.OLEDB.4.0;Data Source =\\192.168.8.125\胶质层测定\Database.mdb;Mode = ReadWrite|Share Deny None;Persist Security Info = False;Jet OLEDB:DataBase Password=",
                        INSTRUMENT = (int)LaboratoryInstrument.JZ_BH,
                        INDEX_ID = (int)LaboratoryIndex.X
                    });

                    NewSyncDataInfo(new CollectModel
                    {
                        AUTO_NO = "ID",
                        TEST_CODE = "FrontFurnaceName",
                        TEST_SAMPLEWEIGHT = "0",
                        TEST_VALUE = "Val(FrontFurnaceY)",
                        TEST_TIME = "CDATE(DateTime)",
                        TABLE = "coal",
                        WHERE = $"FrontFurnaceY IS NOT NULL AND Val(FrontFurnaceY) > 0 AND CDATE(DateTime) >= DateAdd('d',{_iAutoSyncDay},Date())",
                        ConnectionStr = $@"Provider = Microsoft.Jet.OLEDB.4.0;Data Source =\\192.168.8.125\胶质层测定\Database.mdb;Mode = ReadWrite|Share Deny None;Persist Security Info = False;Jet OLEDB:DataBase Password=",
                        INSTRUMENT = (int)LaboratoryInstrument.JZ_BQ,
                        INDEX_ID = (int)LaboratoryIndex.Y
                    });

                    NewSyncDataInfo(new CollectModel
                    {
                        AUTO_NO = "ID",
                        TEST_CODE = "BackFurnaceName",
                        TEST_SAMPLEWEIGHT = "0",
                        TEST_VALUE = "Val(BackFurnaceY)",
                        TEST_TIME = "CDATE(DateTime)",
                        TABLE = "coal",
                        WHERE = $"BackFurnaceY IS NOT NULL AND Val(BackFurnaceY) > 0 AND CDATE(DateTime) >= DateAdd('d',{_iAutoSyncDay},Date())",
                        ConnectionStr = $@"Provider = Microsoft.Jet.OLEDB.4.0;Data Source =\\192.168.8.125\胶质层测定\Database.mdb;Mode = ReadWrite|Share Deny None;Persist Security Info = False;Jet OLEDB:DataBase Password=",
                        INSTRUMENT = (int)LaboratoryInstrument.JZ_BH,
                        INDEX_ID = (int)LaboratoryIndex.Y
                    });

                    #endregion


                    NewSyncDataInfo(new CollectModel
                    {
                        AUTO_NO = "序号",
                        TEST_CODE = "前炉煤样名称",
                        TEST_SAMPLEWEIGHT = "0",
                        TEST_VALUE = "Val(前炉X值)",
                        TEST_TIME = "CDATE(创建时间)",
                        TABLE = "实验结果",
                        WHERE = $"前炉X值 IS NOT NULL AND Val(前炉X值) > 0 AND CDATE(创建时间) >= DateAdd('d',{_iAutoSyncDay},Date())",
                        ConnectionStr = $@"Provider = Microsoft.Jet.OLEDB.4.0;Data Source =\\192.168.8.122\方嘉\jz01.mdb;Mode = ReadWrite|Share Deny None;Persist Security Info = False;Jet OLEDB:DataBase Password=",
                        INSTRUMENT = (int)LaboratoryInstrument.JZ_CQ,
                        INDEX_ID = (int)LaboratoryIndex.X
                    });

                    NewSyncDataInfo(new CollectModel
                    {
                        AUTO_NO = "序号",
                        TEST_CODE = "后炉煤样名称",
                        TEST_SAMPLEWEIGHT = "0",
                        TEST_VALUE = "Val(后炉X值)",
                        TEST_TIME = "CDATE(创建时间)",
                        TABLE = "实验结果",
                        WHERE = $"后炉X值 IS NOT NULL AND Val(后炉X值) > 0 AND CDATE(创建时间) >= DateAdd('d',{_iAutoSyncDay},Date())",
                        ConnectionStr = $@"Provider = Microsoft.Jet.OLEDB.4.0;Data Source =\\192.168.8.122\方嘉\jz01.mdb;Mode = ReadWrite|Share Deny None;Persist Security Info = False;Jet OLEDB:DataBase Password=",
                        INSTRUMENT = (int)LaboratoryInstrument.JZ_CH,
                        INDEX_ID = (int)LaboratoryIndex.X
                    });

                    NewSyncDataInfo(new CollectModel
                    {
                        AUTO_NO = "序号",
                        TEST_CODE = "前炉煤样名称",
                        TEST_SAMPLEWEIGHT = "0",
                        TEST_VALUE = "Val(前炉X值)",
                        TEST_TIME = "CDATE(创建时间)",
                        TABLE = "实验结果",
                        WHERE = $"前炉X值 IS NOT NULL AND Val(前炉X值) > 0 AND CDATE(创建时间) >= DateAdd('d',{_iAutoSyncDay},Date())",
                        ConnectionStr = $@"Provider = Microsoft.Jet.OLEDB.4.0;Data Source =\\192.168.8.124\智能全自动胶质层测定仪_v4.0.1\jz01.mdb;Mode = ReadWrite|Share Deny None;Persist Security Info = False;Jet OLEDB:DataBase Password=",
                        INSTRUMENT = (int)LaboratoryInstrument.JZ_DQ,
                        INDEX_ID = (int)LaboratoryIndex.X
                    });

                    NewSyncDataInfo(new CollectModel
                    {
                        AUTO_NO = "序号",
                        TEST_CODE = "后炉煤样名称",
                        TEST_SAMPLEWEIGHT = "0",
                        TEST_VALUE = "Val(后炉X值)",
                        TEST_TIME = "CDATE(创建时间)",
                        TABLE = "实验结果",
                        WHERE = $"后炉X值 IS NOT NULL AND Val(后炉X值) > 0 AND CDATE(创建时间) >= DateAdd('d',{_iAutoSyncDay},Date())",
                        ConnectionStr = $@"Provider = Microsoft.Jet.OLEDB.4.0;Data Source =\\192.168.8.124\智能全自动胶质层测定仪_v4.0.1\jz01.mdb;Mode = ReadWrite|Share Deny None;Persist Security Info = False;Jet OLEDB:DataBase Password=",
                        INSTRUMENT = (int)LaboratoryInstrument.JZ_DH,
                        INDEX_ID = (int)LaboratoryIndex.X
                    });

                    NewSyncDataInfo(new CollectModel
                    {
                        AUTO_NO = "序号",
                        TEST_CODE = "前炉煤样名称",
                        TEST_SAMPLEWEIGHT = "0",
                        TEST_VALUE = "Val(前炉X值)",
                        TEST_TIME = "CDATE(创建时间)",
                        TABLE = "实验结果",
                        WHERE = $"前炉X值 IS NOT NULL AND Val(前炉X值) > 0 AND CDATE(创建时间) >= DateAdd('d',{_iAutoSyncDay},Date())",
                        ConnectionStr = $@"Provider = Microsoft.Jet.OLEDB.4.0;Data Source =\\192.168.8.121\方嘉\jz01.mdb;Mode = ReadWrite|Share Deny None;Persist Security Info = False;Jet OLEDB:DataBase Password=",
                        INSTRUMENT = (int)LaboratoryInstrument.JZ_EQ,
                        INDEX_ID = (int)LaboratoryIndex.X
                    });

                    NewSyncDataInfo(new CollectModel
                    {
                        AUTO_NO = "序号",
                        TEST_CODE = "后炉煤样名称",
                        TEST_SAMPLEWEIGHT = "0",
                        TEST_VALUE = "Val(后炉X值)",
                        TEST_TIME = "CDATE(创建时间)",
                        TABLE = "实验结果",
                        WHERE = $"后炉X值 IS NOT NULL AND Val(后炉X值) > 0 AND CDATE(创建时间) >= DateAdd('d',{_iAutoSyncDay},Date())",
                        ConnectionStr = $@"Provider = Microsoft.Jet.OLEDB.4.0;Data Source =\\192.168.8.121\方嘉\jz01.mdb;Mode = ReadWrite|Share Deny None;Persist Security Info = False;Jet OLEDB:DataBase Password=",
                        INSTRUMENT = (int)LaboratoryInstrument.JZ_EH,
                        INDEX_ID = (int)LaboratoryIndex.X
                    });
                    #region 192.168.8.127 智能全自动胶质层测定仪_v4
                    NewSyncDataInfo(new CollectModel
                    {
                        AUTO_NO = "序号",
                        TEST_CODE = "前炉煤样名称",
                        TEST_SAMPLEWEIGHT = "0",
                        TEST_VALUE = "Val(前炉X值)",
                        TEST_TIME = "CDATE(创建时间)",
                        TABLE = "实验结果",
                        WHERE = $"前炉X值 IS NOT NULL AND Val(前炉X值) > 0 AND CDATE(创建时间) >= DateAdd('d',{_iAutoSyncDay},Date())",
                        ConnectionStr = $@"Provider = Microsoft.Jet.OLEDB.4.0;Data Source =\\192.168.8.127\智能全自动胶质层测定仪_v4.0.1_不带版权\jz01.mdb;Mode = ReadWrite|Share Deny None;Persist Security Info = False;Jet OLEDB:DataBase Password=",
                        INSTRUMENT = 63,
                        INDEX_ID = (int)LaboratoryIndex.X
                    });

                    NewSyncDataInfo(new CollectModel
                    {
                        AUTO_NO = "序号",
                        TEST_CODE = "后炉煤样名称",
                        TEST_SAMPLEWEIGHT = "0",
                        TEST_VALUE = "Val(后炉X值)",
                        TEST_TIME = "CDATE(创建时间)",
                        TABLE = "实验结果",
                        WHERE = $"后炉X值 IS NOT NULL AND Val(后炉X值) > 0 AND CDATE(创建时间) >= DateAdd('d',{_iAutoSyncDay},Date())",
                        ConnectionStr = $@"Provider = Microsoft.Jet.OLEDB.4.0;Data Source =\\192.168.8.127\智能全自动胶质层测定仪_v4.0.1_不带版权\jz01.mdb;Mode = ReadWrite|Share Deny None;Persist Security Info = False;Jet OLEDB:DataBase Password=",
                        INSTRUMENT = 64,
                        INDEX_ID = (int)LaboratoryIndex.X
                    });
                    #endregion
                    NewSyncDataInfo(new CollectModel
                    {
                        AUTO_NO = "AutoNo",
                        TEST_CODE = "SampleNo",
                        TEST_SAMPLEWEIGHT = "0",
                        TEST_VALUE = "Val(Stad)",
                        TEST_TIME = "CDATE(TestDate)",
                        TABLE = "TestData",
                        WHERE = $"Stad IS NOT NULL AND Val(Stad) > 0 AND CDATE(TestDate) >= DateAdd('d',{_iAutoSyncDay},Date())",
                        ConnectionStr = $@"Provider = Microsoft.ACE.OLEDB.12.0;Data Source =\\192.168.8.114\Sundy\SDS自动定硫仪\DataBase\PxmAData.mdb;Mode = ReadWrite|Share Deny None;Persist Security Info = False;Jet OLEDB:DataBase Password=199302",
                        INSTRUMENT = 9,
                        INDEX_ID = 1012
                    });
                    NewSyncDataInfo(new CollectModel
                    {
                        AUTO_NO = "ID",
                        TEST_CODE = "煤样名称",
                        TEST_SAMPLEWEIGHT = "0",
                        TEST_VALUE = "Val(G值)",
                        TEST_TIME = "CDATE(测量时间)",
                        TABLE = "实验数据",
                        WHERE = $"G值 IS NOT NULL AND Val(G值) > 0 AND CDATE(测量时间) >= DateAdd('d',{_iAutoSyncDay},Date())",
                        ConnectionStr = $@"Provider = Microsoft.Jet.OLEDB.4.0;Data Source =\\192.168.8.153\全自动粘结指数测定系统\db\fj.mdb;Mode = ReadWrite|Share Deny None;Persist Security Info = False;Jet OLEDB:DataBase Password=",
                        INSTRUMENT = 41,//粘结指数测定仪C
                        INDEX_ID = (int)LaboratoryIndex.G
                    });
                    //NewSyncDataInfo(new CollectModel
                    //{
                    //    AUTO_NO = "序号",
                    //    TEST_CODE = "前炉煤样名称",
                    //    TEST_SAMPLEWEIGHT = "0",
                    //    TEST_VALUE = "Val(前炉X值)",
                    //    TEST_TIME = "CDATE(创建时间)",
                    //    TABLE = "实验结果",
                    //    WHERE = "前炉X值 IS NOT NULL AND Val(前炉X值) > 0 AND CDATE(创建时间) >= DateAdd('d',-2,Date())",
                    //    ConnectionStr = $@"Provider = Microsoft.Jet.OLEDB.4.0;Data Source = E:\\jz01.mdb;Mode = ReadWrite|Share Deny None;Persist Security Info = False;Jet OLEDB:DataBase Password=",
                    //    INSTRUMENT = (int)LaboratoryInstrument.JZ_DQ,
                    //    INDEX_ID = (int)LaboratoryIndex.X
                    //});

                    //NewSyncDataInfo(new CollectModel
                    //{
                    //    AUTO_NO = "序号",
                    //    TEST_CODE = "后炉煤样名称",
                    //    TEST_SAMPLEWEIGHT = "0",
                    //    TEST_VALUE = "Val(后炉X值)",
                    //    TEST_TIME = "CDATE(创建时间)",
                    //    TABLE = "实验结果",
                    //    WHERE = "后炉X值 IS NOT NULL AND Val(后炉X值) > 0 AND CDATE(创建时间) >= DateAdd('d',-2,Date())",
                    //    ConnectionStr = $@"Provider = Microsoft.Jet.OLEDB.4.0;Data Source = E:\\jz01.mdb;Mode = ReadWrite|Share Deny None;Persist Security Info = False;Jet OLEDB:DataBase Password=",
                    //    INSTRUMENT = (int)LaboratoryInstrument.JZ_DH,
                    //    INDEX_ID = (int)LaboratoryIndex.X
                    //});
                    run = false;
                    //Thread.Sleep(1000 * 60 * 5);
                }

                var runTimeEnd = System.DateTime.Now.Ticks;
                var runTime = (runTimeEnd - runTimeBegin) / 10000000.0;//秒
                var count = (int)(runTime * 2);//500毫秒个数 
                while (!_ctsSyncThreadAuto.Token.IsCancellationRequested && iTick < (_iAutoSyncTimeGap * 60 * 2 - count))
                {
                    Thread.Sleep(500);
                    iTick++;
                }
                iTick = 0;
            }
            _bSyncThreadRunning = false;
        }

        /// <summary>
        /// 自动同步线程执行函数
        /// </summary>
        private void StartSyncThreadAuto_old()
        {
            //线程已经开始运行    
            _bSyncThreadRunning = true;
            var iTick = 0;
            while (!_ctsSyncThreadAuto.Token.IsCancellationRequested)
            {
                var runTimeBegin = System.DateTime.Now.Ticks;

                var index = LaboratoryIndex.QBAD;
                var num = 0;
                var run = true;
                while (run)
                {
                    if (_ctsSyncThreadAuto.Token.IsCancellationRequested)
                    {/////是否取消任务，立即退出线程
                        _bSyncThreadRunning = false;
                        return;
                    }
                    switch (index)
                    {
                        case LaboratoryIndex.QBAD:
                            var instrument = _dtLaboratoryInfo[index];
                            SyncDataInfo(index, instrument[num]);
                            if (num == instrument.Count - 1)
                            {
                                index = LaboratoryIndex.STAD;
                                num = 0;
                            }
                            else
                                num++;
                            break;
                        case LaboratoryIndex.STAD:
                            instrument = _dtLaboratoryInfo[index];
                            SyncDataInfo(index, instrument[num]);
                            if (num == instrument.Count - 1)
                            {
                                index = LaboratoryIndex.MT;
                                num = 0;
                            }
                            else
                                num++;
                            break;
                        case LaboratoryIndex.MT:
                            instrument = _dtLaboratoryInfo[index];
                            SyncDataInfo(index, instrument[num]);
                            if (num == instrument.Count - 1)
                            {
                                index = LaboratoryIndex.G;
                                num = 0;
                            }
                            else
                                num++;
                            break;

                        case LaboratoryIndex.G:
                            instrument = _dtLaboratoryInfo[index];
                            SyncDataInfo(index, instrument[num]);
                            if (num == instrument.Count - 1)
                            {
                                index = LaboratoryIndex.Y;
                                num = 0;
                            }
                            else
                                num++;
                            break;
                        case LaboratoryIndex.Y:
                            instrument = _dtLaboratoryInfo[index];
                            SyncDataInfo(index, instrument[num]);
                            if (num == instrument.Count - 1)
                            {
                                index = LaboratoryIndex.MAD;
                                num = 0;
                            }
                            else
                                num++;
                            break;
                        case LaboratoryIndex.MAD:
                        case LaboratoryIndex.AAD:
                        case LaboratoryIndex.VAD:
                            instrument = _dtLaboratoryInfo[LaboratoryIndex.MAD];//MAD,AAD,VAD结果在同一台仪器上
                            SyncDataInfo(index, instrument[num]);
                            if (num == instrument.Count - 1)
                            {
                                num = 0;
                                switch (index)
                                {
                                    case LaboratoryIndex.MAD:
                                        index = LaboratoryIndex.AAD;
                                        break;
                                    case LaboratoryIndex.AAD:
                                        index = LaboratoryIndex.VAD;
                                        break;
                                    case LaboratoryIndex.VAD:
                                        run = false;
                                        break;
                                }
                            }
                            else
                                num++;
                            break;

                        case LaboratoryIndex.CRC:
                            run = false;
                            break;
                        default:
                            run = false;
                            break;
                    }
                }

                var runTimeEnd = System.DateTime.Now.Ticks;
                var runTime = (runTimeEnd - runTimeBegin) / 10000000.0;//秒
                var count = (int)(runTime * 2);//500毫秒个数 
                while (!_ctsSyncThreadAuto.Token.IsCancellationRequested && iTick < (_iAutoSyncTimeGap * 60 * 2 - count))
                {
                    Thread.Sleep(500);
                    iTick++;
                }
                iTick = 0;
            }
            _bSyncThreadRunning = false;
        }


        private void NewSyncDataInfo(CollectModel model)
        {
            string currentIp = "";
            try
            {
                //优先pingIP判断电脑是否开机，防止连接超时过长
                var m = Regex.Match(model.ConnectionStr, @"\d{1,3}.\d{1,3}.\d{1,3}.\d{1,3}");
                if (m.Success)
                {
                    currentIp = m.Value;
                    var pingSender = new Ping();
                    var reply = pingSender.Send(m.Value, 100);//第一个参数为ip地址，第二个参数为ping的时间
                    if (reply != null && reply.Status != IPStatus.Success)
                    {
                        Titan.FIS.Log.LogHelper.DoWrite(Titan.FIS.Log.LogType.Run, $"IP:{m.Value}连接超时！");
                        FormHelper.ApplicationShow(ricMsg, DisplayType.Error, $"IP:{m.Value}连接超时！");
                        return;
                    }

                }

                Titan.FIS.Log.LogHelper.DoWrite(Titan.FIS.Log.LogType.Run, $"开始同步{m.Value} 数据！");
                FormHelper.ApplicationShow(ricMsg, DisplayType.Error, $"---------------------------------------------");
                FormHelper.ApplicationShow(ricMsg, DisplayType.Error, $"开始同步{m.Value}数据！");
                string SQL = $"SELECT {model.AUTO_NO} AS AUTO_NO,{model.TEST_CODE} AS TEST_CODE,{model.TEST_SAMPLEWEIGHT} AS TEST_SAMPLEWEIGHT,{model.TEST_VALUE} AS TEST_VALUE, {model.TEST_TIME} AS TEST_TIME FROM {model.TABLE} WHERE {model.WHERE}";

                OleDbDataAdapter oda = null;
                DataSet ds = null;
                var accessCon = new OleDbConnection(model.ConnectionStr);
                accessCon.Open();
                ds = new DataSet();
                oda = new OleDbDataAdapter(SQL, accessCon);
                oda.Fill(ds, "TableSql");
                var accessData = ds.Tables["TableSql"];
                accessCon.Close();
                if (accessData.Rows.Count < 1)
                    return;

                List<CollectModel> list = new List<CollectModel>();
                for (int i = 0; i < accessData.Rows.Count; i++)
                {
                    list.Add(new CollectModel
                    {
                        AUTO_NO = accessData.Rows[i]["AUTO_NO"].ToString(),
                        INSTRUMENT = model.INSTRUMENT,
                        TEST_CODE = accessData.Rows[i]["TEST_CODE"].ToString(),
                        TEST_TIME = accessData.Rows[i]["TEST_TIME"].ToString(),
                        INDEX_ID = model.INDEX_ID,
                        TEST_SAMPLEWEIGHT = accessData.Rows[i]["TEST_SAMPLEWEIGHT"].ToString(),
                        TEST_VALUE = accessData.Rows[i]["TEST_VALUE"].ToString(),
                    });
                }
                string insertSql = "";
                string selectSql = "";
                string updateSql = "";

                //var oraConn = new OracleConnection { ConnectionString = CfgHelper.DBConnStr };
                //oraConn.Open();

                OracleCommand oracleCommand;

                using (OracleConnection oraConn = new OracleConnection(CfgHelper.DBConnStr))
                {
                    if (oraConn.State != ConnectionState.Open)
                    {
                        oraConn.Open();
                    }
                    list.ForEach(res =>
                    {
                        try
                        {
                            if (!string.IsNullOrEmpty(res.TEST_CODE))
                            {
                                //selectSql = $@"SELECT * FROM LMS_TESTDATA_COLLECTION WHERE AUTO_NO='{res.AUTO_NO}' AND INDEX_ID = {res.INDEX_ID} AND INSTRUMENT = {res.INSTRUMENT} AND TEST_CODE = '{res.TEST_CODE}'";
                                selectSql = $@"SELECT * FROM LMS_TESTDATA_COLLECTION WHERE AUTO_NO='{res.AUTO_NO}' AND INDEX_ID = {res.INDEX_ID} AND INSTRUMENT = {res.INSTRUMENT} AND TEST_CODE = '{res.TEST_CODE}'";
                                updateSql = $@"UPDATE LMS_TESTDATA_COLLECTION SET TEST_VALUE = {res.TEST_VALUE} WHERE INDEX_ID = {res.INDEX_ID} AND INSTRUMENT = {res.INSTRUMENT} AND TEST_CODE = '{res.TEST_CODE}' AND AUTO_NO='{res.AUTO_NO}'";
                                insertSql = $@"INSERT INTO LMS_TESTDATA_COLLECTION
                                  (COLLECTION_ID,INSTRUMENT,AUTO_NO,TEST_CODE,INDEX_ID,TEST_VALUE,STATUS,COLLECTION_TIME,TEST_TIME,TEST_SAMPLEWEIGHT)
                                        VALUES
                                  (SYS_GUID(),{res.INSTRUMENT}, '{res.AUTO_NO}', '{res.TEST_CODE}',{res.INDEX_ID},{res.TEST_VALUE},0,SYSDATE,:pTEST_TIME,0)";

                                oracleCommand = new OracleCommand(selectSql, oraConn);
                                var dap = new OracleDataAdapter(oracleCommand);
                                System.Data.DataSet dataSet = new System.Data.DataSet();
                                dap.Fill(dataSet, "TableSql");
                                //查询已经存在的数据
                                System.Data.DataTable oraData = dataSet.Tables[0];
                                if (DtIsNull(oraData))
                                {
                                    //如果是已经存在数据
                                    if (res.TEST_VALUE != oraData.Rows[0]["TEST_VALUE"].ToString())
                                    {
                                        if (oraData.Rows[0]["STATUS"].ToString() == "0")//只有没有采用或者弃用的数据才可以被修改
                                        {
                                            //update
                                            oracleCommand.CommandText = updateSql;
                                            int result = oracleCommand.ExecuteNonQuery();
                                            if (result > 0)
                                            {
                                                Titan.FIS.Log.LogHelper.DoWrite(Titan.FIS.Log.LogType.Run, string.Format("成功同步{0},{1},{2}数据{3}条.", currentIp, model.INSTRUMENT, model.INDEX_ID, result));
                                            }
                                        }
                                    }
                                }
                                else
                                {
                                    //不存在 则新增数据
                                    oracleCommand.Parameters.Add(new OracleParameter("pTEST_TIME", OracleDbType.Date)
                                    {
                                        SourceColumn = "TEST_TIME",
                                        SourceVersion = DataRowVersion.Current,
                                        Value = ToDate(res.TEST_TIME)
                                    });
                                    oracleCommand.CommandText = insertSql;
                                    int result = oracleCommand.ExecuteNonQuery();
                                    if (result > 0)
                                    {
                                        Titan.FIS.Log.LogHelper.DoWrite(Titan.FIS.Log.LogType.Run, string.Format("成功同步{0},{1},{2}数据{3}条.", currentIp, model.INSTRUMENT, model.INDEX_ID, result));
                                        FormHelper.ApplicationShow(ricMsg, DisplayType.Error, string.Format("成功同步{0},{1},{2}数据{3}条.", currentIp, model.INSTRUMENT, model.INDEX_ID, result));
                                    }
                                }
                                #region old

                                //if (DtIsNull(oraData))
                                //{
                                //    using (OracleCommand cmd = oraConn.CreateCommand())
                                //    {

                                //        if (oraData.Rows.Count > 0)
                                //        {
                                //            if (res.TEST_VALUE != oraData.Rows[0]["TEST_VALUE"].ToString())
                                //            {
                                //                if (oraData.Rows[0]["STATUS"].ToString() == "0")//只有没有采用或者弃用的数据才可以被修改
                                //                {
                                //                    //update
                                //                    cmd.CommandText = updateSql;
                                //                    int result = cmd.ExecuteNonQuery();
                                //                    if (result > 0)
                                //                    {
                                //                        Titan.FIS.Log.LogHelper.DoWrite(Titan.FIS.Log.LogType.Run, string.Format("成功同步{0},{1},{2}数据{3}条.", currentIp, model.INSTRUMENT, model.INDEX_ID, result));
                                //                    }
                                //                }
                                //            }
                                //        }
                                //        else
                                //        {
                                //            //insert
                                //            //using (OracleTransaction transaction = oraConn.BeginTransaction())
                                //            cmd.Parameters.Add(new OracleParameter("pTEST_TIME", OracleDbType.Date)
                                //            {
                                //                SourceColumn = "TEST_TIME",
                                //                SourceVersion = DataRowVersion.Current,
                                //                Value = ToDate(res.TEST_TIME)
                                //            });
                                //            cmd.CommandText = insertSql;
                                //            int result = cmd.ExecuteNonQuery();
                                //            if (result > 0)
                                //            {
                                //                Titan.FIS.Log.LogHelper.DoWrite(Titan.FIS.Log.LogType.Run, string.Format("成功同步{0},{1},{2}数据{3}条.", currentIp, model.INSTRUMENT, model.INDEX_ID, result));
                                //                FormHelper.ApplicationShow(ricMsg, DisplayType.Error, string.Format("成功同步{0},{1},{2}数据{3}条.", currentIp, model.INSTRUMENT, model.INDEX_ID, result));
                                //            }
                                //        }
                                //    }
                                //}

                                #endregion
                            }
                        }
                        catch
                        {
                        }
                    });
                }
            }
            catch (Exception ex)
            {
                Titan.FIS.Log.LogHelper.DoWrite(Titan.FIS.Log.LogType.Error, string.Format("同步{0}数据库失败，请检查网络，原因：{1}", currentIp, ex));
                FormHelper.ApplicationShow(ricMsg, DisplayType.Error, string.Format("同步{0}数据库失败，请检查网络，原因：{1}", currentIp, ex));
            }
            finally
            {
                Titan.FIS.Log.LogHelper.DoWrite(Titan.FIS.Log.LogType.Run, $"结束同步{currentIp}数据！");
                FormHelper.ApplicationShow(ricMsg, DisplayType.Error, $"结束同步{currentIp}数据");
                FormHelper.ApplicationShow(ricMsg, DisplayType.Error, $"---------------------------------------------");
            }
        }

        public bool DtIsNull(System.Data.DataTable dataTable)
        {

            if (dataTable == null)
                return false;
            if (dataTable.Rows.Count <= 0)
                return false;
            return true;
        }

        public DateTime ToDate(string date)
        {
            DateTime dateTime = Convert.ToDateTime("1900-01-01 00:00:01");
            DateTime.TryParse(date, out dateTime);
            return dateTime;
        }

        /// <summary>
        /// 同步数据库方法
        /// </summary>
        private void SyncDataInfo(LaboratoryIndex index, LaboratoryInstrument belong)
        {
            try
            {
                var ListItemTmp = _dtLaboratoryDataBaseInfo[belong];
                var ListItemTmpIndex = _dtLaboratoryDataBaseIndex[index];
                //string LastSyncTime = "1970/1/1 00:00:00";
                var strYesterDay = System.DateTime.Now.AddDays(-3).ToString("yyyy-MM-dd");
                var LastSyncAutoNo = strYesterDay + " 00:00:00";
                var sourcePath = ListItemTmp.Name.Replace("2016", System.DateTime.Now.Year.ToString());

                //优先pingIP判断电脑是否开机，防止连接超时过长
                var m = Regex.Match(sourcePath, @"\d{1,3}.\d{1,3}.\d{1,3}.\d{1,3}");
                if (m.Success)
                {
                    var pingSender = new Ping();
                    var reply = pingSender.Send(m.Value, 100);//第一个参数为ip地址，第二个参数为ping的时间
                    if (reply != null && reply.Status != IPStatus.Success)
                        return;
                }

                var source1 = @"Provider = Microsoft.Jet.OLEDB.4.0;Data Source =" + sourcePath + ";Mode = ReadWrite|Share Deny None;Persist Security Info = False;Jet OLEDB:DataBase Password=" + ListItemTmp.Value2 + ";";//"User ID=ListItemTmp.Value1;   
                var source2 = @"Provider = Microsoft.Jet.OLEDB.4.0;Data Source =" + sourcePath + ";Persist Security Info = False;Jet OLEDB:DataBase Password=" + ListItemTmp.Value2 + ";";//"User ID=ListItemTmp.Value1; 
                var source3 = @"Provider = Microsoft.Jet.OLEDB.4.0;Data Source =" + sourcePath + ";Jet OLEDB:Database Locking Mode=0;Persist Security Info = False;Jet OLEDB:DataBase Password=" + ListItemTmp.Value2 + ";";//"User ID=ListItemTmp.Value1; 
                var strSql = string.Format("select {0} from {1} where {2} >= {3}", ListItemTmpIndex.Name, ListItemTmp.Value3, ListItemTmpIndex.Value1, "#" + LastSyncAutoNo + "#");

                OleDbDataAdapter oda = null;
                DataSet ds = null;
                var accessCon = new OleDbConnection(source1);

                if (sourcePath.Contains("192.168.8.201"))
                {
                    var strSelectYColumn = @"SpNumber as AutoNo,
                                    SpName as TestCode,
                                    SpWeight as SampleWeight,
                                    Val(SpSad) as TestValue, 
                                    CDATE(SpDateTime) AS TestDate
                                   ";

                    const string strWhereYColumn = "SpSad IS NOT NULL AND Val(SpSad) > 0 AND CDATE(SpDateTime) >= DateAdd('d',-2,Date())";

                    //strSql = $"select {strSelectYColumn} from {ListItemTmp.Value3} where {strWhereYColumn} >= {"#" + LastSyncAutoNo + "#"}";
                    strSql = $"select {strSelectYColumn} from {ListItemTmp.Value3} where {strWhereYColumn}";
                    //strSql = $"select {strSelectYColumn} from {ListItemTmp.Value3}";
                }
                if (sourcePath.Contains(".124"))
                {


                }
                if (sourcePath.Contains(".121") || sourcePath.Contains(".122") || sourcePath.Contains(".124") || sourcePath.Contains(".125") || sourcePath.Contains(".126"))
                {
                    strSql = $@"SELECT 编号 as AutoNo,
                                    YangName as TestCode,
                                    0 as SampleWeight,                         
                                    Val(HouMax) as TestValue,            
                                    CDATE(CeTime) AS TestDate FROM (Select * from PrintH where CeTime IS NOT NULL AND CeTime <> '' AND HouMax IS NOT NULL AND Val(HouMax) > 0) T     
                                    WHERE CDATE(CeTime) >= DateAdd('d',-2,Date())>= {"#" + LastSyncAutoNo + "#"}";

                    //strSql = $@"SELECT 编号 as AutoNo,
                    //                YangName as TestCode,
                    //                0 as SampleWeight,                         
                    //                Val(HouMax) as TestValue,            
                    //                CDATE(CeTime) AS TestDate FROM (Select * from PrintH where CeTime IS NOT NULL AND CeTime <> '' AND HouMax IS NOT NULL AND Val(HouMax) > 0) T     
                    //                WHERE CDATE(CeTime) >= DateAdd('d',-2,Date())";
                }
                try
                {
                    accessCon.Open();
                }
                catch (Exception)
                {
                    try
                    {
                        accessCon.Dispose();
                        accessCon = new OleDbConnection(source2);
                        accessCon.Open();
                    }
                    catch (Exception)
                    {
                        accessCon.Dispose();
                        accessCon = new OleDbConnection(source3);
                        accessCon.Open();
                    }
                }
                ds = new DataSet();
                oda = new OleDbDataAdapter(strSql, accessCon);
                oda.Fill(ds, "TableSql");
                var accessData = ds.Tables["TableSql"];
                accessCon.Close();
                if (accessData.Rows.Count < 1)
                    return;

                ////Oracle批量插入表到数据库中  
                var strSqlSelect = string.Format(@"select COLLECTION_ID,AUTO_NO,TEST_CODE,TEST_VALUE,TEST_SAMPLEWEIGHT,TEST_TIME
                                                    from LMS_TESTDATA_COLLECTION where COLLECTION_TIME > TO_DATE('{0}', 'yyyy-MM-dd HH24:mi:ss') 
                                            and INSTRUMENT = {1} and INDEX_ID = {2}", LastSyncAutoNo, (int)belong, (int)index);
                var strSqlUpdate = @"Update LMS_TESTDATA_COLLECTION set TEST_CODE = :pTEST_CODE,
                                                                                TEST_VALUE = :pTEST_VALUE,
                                                                                TEST_SAMPLEWEIGHT = :pTEST_SAMPLEWEIGHT,
                                                                                TEST_TIME = :pTEST_TIME                                                                              
                                                                where COLLECTION_ID = :pOriginal_COLLECTION_ID ";
                var oraConn = new OracleConnection { ConnectionString = CfgHelper.DBConnStr };
                oraConn.Open();
                var selectCommand = new OracleCommand(strSqlSelect, oraConn);
                var dap = new OracleDataAdapter(selectCommand);
                var updateCommand = new OracleCommand(strSqlUpdate, oraConn);
                var updatePara = new OracleParameter("pTEST_CODE", OracleDbType.Varchar2)
                {
                    SourceColumn = "TEST_CODE",
                    SourceVersion = DataRowVersion.Current
                };
                updateCommand.Parameters.Add(updatePara);
                updatePara = new OracleParameter("pTEST_VALUE", OracleDbType.Decimal)
                {
                    SourceColumn = "TEST_VALUE",
                    SourceVersion = DataRowVersion.Current
                };
                updateCommand.Parameters.Add(updatePara);
                updatePara = new OracleParameter("pTEST_SAMPLEWEIGHT", OracleDbType.Decimal)
                {
                    SourceColumn = "TEST_SAMPLEWEIGHT",
                    SourceVersion = DataRowVersion.Current
                };
                updateCommand.Parameters.Add(updatePara);
                updatePara = new OracleParameter("pTEST_TIME", OracleDbType.Date)
                {
                    SourceColumn = "TEST_TIME",
                    SourceVersion = DataRowVersion.Current
                };
                updateCommand.Parameters.Add(updatePara);
                updatePara = new OracleParameter("pOriginal_COLLECTION_ID", OracleDbType.Varchar2)
                {
                    SourceColumn = "COLLECTION_ID",
                    SourceVersion = DataRowVersion.Original
                };
                updateCommand.Parameters.Add(updatePara);
                dap.UpdateCommand = updateCommand;

                var dso = new DataSet();
                dap.Fill(dso, "TableOracle");//将select语句查询出来的数据填充到TableOracle表中
                var dto = dso.Tables["TableOracle"];

                IList<DataRow> tmpDataRows = new List<DataRow>();//存储需要插入的数据   
                                                                 //更新不同的数据
                for (var j = 0; j < accessData.Rows.Count; j++)
                {
                    var AutoNoTmp = accessData.Rows[j]["AutoNo"].ToString();
                    var drDt = dto.Select(string.Format(@"AUTO_NO = '{0}'", AutoNoTmp));//用Select就不需要建立主键
                    if (drDt.Length > 0)//正常时一般为1
                    {
                        foreach (var drDtTmp in drDt)
                        {
                            if (drDtTmp["TEST_CODE"].ToString() != accessData.Rows[j]["TestCode"].ToString())
                                drDtTmp["TEST_CODE"] = accessData.Rows[j]["TestCode"].ToString();
                            if (!string.IsNullOrEmpty(accessData.Rows[j]["TestValue"].ToString()) && decimal.Parse(drDtTmp["TEST_VALUE"].ToString()).ToString("#0.00") != decimal.Parse(accessData.Rows[j]["TestValue"].ToString()).ToString("#0.00"))
                                drDtTmp["TEST_VALUE"] = accessData.Rows[j]["TestValue"];
                            if (!string.IsNullOrEmpty(accessData.Rows[j]["SampleWeight"].ToString()) && decimal.Parse(drDtTmp["TEST_SAMPLEWEIGHT"].ToString()).ToString("#0.00") != decimal.Parse(accessData.Rows[j]["SampleWeight"].ToString()).ToString("#0.00"))
                                drDtTmp["TEST_SAMPLEWEIGHT"] = accessData.Rows[j]["SampleWeight"];
                            if (!string.IsNullOrEmpty(accessData.Rows[j]["TestDate"].ToString()) && drDtTmp["TEST_TIME"].ToString() != accessData.Rows[j]["TestDate"].ToString())
                                drDtTmp["TEST_TIME"] = System.DateTime.Parse(accessData.Rows[j]["TestDate"].ToString());
                        }
                    }
                    else
                        tmpDataRows.Add(accessData.Rows[j]);
                }

                // return;//屏蔽提交到ORACLE数据库
                dap.Update(dso.Tables["TableOracle"]);
                oraConn.Close();

                var result = 0;
                if (tmpDataRows.Count > 0)
                {
                    foreach (DataRow dataRow in tmpDataRows)
                    {
                        if (_ctsSyncThreadAuto.Token.IsCancellationRequested)
                        {/////是否取消任务，立即退出线程
                            return;
                        }
                        var errMsg = string.Empty;
                        if (DataOperator.DoSaveLmsIndexValue(belong, dataRow["AutoNo"].ToString(), dataRow["TestCode"].ToString(), index,
                            dataRow["TestValue"].ToString(), dataRow["SampleWeight"].ToString(), dataRow["TestDate"].ToString(), ref errMsg))
                            result++;
                        else
                            Titan.FIS.Log.LogHelper.DoWrite(Titan.FIS.Log.LogType.Error, string.Format("同步{0}化验数据失败，原因：{1}", sourcePath, errMsg));
                    }
                    if (result > 0)
                    {
                        Titan.FIS.Log.LogHelper.DoWrite(Titan.FIS.Log.LogType.Run, string.Format("成功同步{0},{1},{2}数据{3}条.", sourcePath, belong, index, result));
                    }
                }
            }
            catch (Exception ex)
            {
                Titan.FIS.Log.LogHelper.DoWrite(Titan.FIS.Log.LogType.Error, string.Format("同步{0}数据库失败，请检查网络，原因：{1}", belong, ex));
            }
        }

    }
}
